#include "mainwindow.h"
#include "ui_mainwindow.h"

auto my_selection_mode=QItemSelectionModel::Clear|QItemSelectionModel::Select|QItemSelectionModel::Rows|QItemSelectionModel::Current;

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    db=QSqlDatabase::addDatabase("QSQLITE", "first");///这里有个细节：db.addDatabase("QSQLITE")的写法是错误的
    temp_db=QSqlDatabase::addDatabase("QSQLITE", "second");///这里有个细节：db.addDatabase("QSQLITE")的写法是错误的
    db.close();
    query=QSqlQuery(db);
}

MainWindow::~MainWindow()
{
    delete ui;
}

/*选择变化函数*/
void MainWindow::change_MatSelection(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    ui->spinboxRow->setValue(current.row()+1);
    ui->lineEdit->setText("Materials");
}
void MainWindow::change_TypeSelection(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    ui->spinboxRow->setValue(current.row()+1);
    ui->lineEdit->setText("Type");
}
void MainWindow::change_SizeSelection(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    ui->spinboxRow->setValue(current.row()+1);
    ui->lineEdit->setText("Size");
}

/*新建数据库*/
void MainWindow::on_actionNew_triggered()
{
    QString temp=QFileDialog::getSaveFileName(this, \
        QStringLiteral("选择位置"),\
        QCoreApplication::applicationDirPath(),"数据库(*.db)");
    if(temp.isEmpty())  return;
    db.close();
    db.setDatabaseName(temp);
    if(db.open())
    {
        /*新建表格*/
        query.exec("CREATE TABLE Materials (\
                   id      INTEGER      PRIMARY KEY,\
                   name    VARCHAR (10),\
                   mbox    INTEGER (1),\
                   mrow    INTEGER (1),\
                   mcol    INTEGER (1),\
                   type_id              REFERENCES Type (type_id) \
                                        DEFAULT (0),\
                   size_id              REFERENCES Size (size_id) \
                                        DEFAULT (0) \
               );");
        query.exec("CREATE TABLE Size (\
                   size_id   INTEGER      PRIMARY KEY,\
                   size_name VARCHAR (10) \
               );");
        query.exec("CREATE TABLE Type (\
                   type_id   INTEGER      PRIMARY KEY,\
                   type_name VARCHAR (10) \
               );");
        query.exec("insert into Type (type_id,type_name)values(0,'未选择类型')");
        query.exec("insert into Size (size_id,size_name)values(0,'未选择规格')");

        if(init_table())
        {
            QString str="物料数据库系统  "+temp;
            DB_path=temp;
            this->setWindowTitle(str);
        }
        else
        {
            db.close();
        }
    }
}

/*打开数据库*/
void MainWindow::on_actionOpen_triggered()
{
    QString temp=QFileDialog::getOpenFileName(this, \
        QStringLiteral("选择数据库"),\
        QCoreApplication::applicationDirPath(),"数据库(*.db)");
    if(temp.isEmpty())  return;
    /*数据库初始化*/
    db.close();
    db.setDatabaseName(temp);
    if(db.open())
    {
        if(init_table())
        {
            QString str="物料数据库系统  "+temp;
            DB_path=temp;
            this->setWindowTitle(str);
        }
        else
        {
            db.close();
        }
    }
}

/*初始物料*/
bool MainWindow::init_table()
{
    /*********************************************************************************/
    tabTable=new QSqlRelationalTableModel(this,db);
    tabTable->setTable("Materials");
    tabTable->setSort(tabTable->fieldIndex("id"),Qt::AscendingOrder);
    tabTable->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabTable->select()))
    {
        return false;
    }

    tabTable->setHeaderData(tabTable->fieldIndex("id"),Qt::Horizontal,"编号(id)");
    tabTable->setHeaderData(tabTable->fieldIndex("name"),Qt::Horizontal,"名称(name)");
    tabTable->setHeaderData(tabTable->fieldIndex("mbox"),Qt::Horizontal,"盒子(mbox)");
    tabTable->setHeaderData(tabTable->fieldIndex("mrow"),Qt::Horizontal,"行(mrow)");
    tabTable->setHeaderData(tabTable->fieldIndex("mcol"),Qt::Horizontal,"列(mcol)");
    tabTable->setHeaderData(tabTable->fieldIndex("type_id"),Qt::Horizontal,"类型");
    tabTable->setHeaderData(tabTable->fieldIndex("size_id"),Qt::Horizontal,"规格");

    tabTable->setRelation(tabTable->fieldIndex("type_id"),QSqlRelation("Type","type_id","type_name"));
    tabTable->setRelation(tabTable->fieldIndex("size_id"),QSqlRelation("Size","size_id","size_name"));

    ui->tableView->setModel(tabTable);
    for(int i=0;i<tabTable->columnCount();i++)
        ui->tableView->setColumnWidth(i,150);

    MatSelection=new QItemSelectionModel(tabTable);
    ui->tableView->setSelectionModel(MatSelection);
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    connect(MatSelection,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
            this,SLOT(change_MatSelection(QModelIndex,QModelIndex)));

    ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));

    tabTable->select();
    /*********************************************************************************/
    tabType=new QSqlTableModel(this,db);
    tabType->setTable("Type");
    tabType->setSort(tabType->fieldIndex("id"),Qt::AscendingOrder);
    tabType->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabType->select()))
    {
        return false;
    }

    tabType->setHeaderData(tabType->fieldIndex("type_id"),Qt::Horizontal,"编号(type_id)");
    tabType->setHeaderData(tabType->fieldIndex("type_name"),Qt::Horizontal,"类型(type_name)");

    ui->tableView_2->setModel(tabType);
    for(int i=0;i<tabType->columnCount();i++)
        ui->tableView_2->setColumnWidth(i,300);

    TypeSelection=new QItemSelectionModel(tabType);
    ui->tableView_2->setSelectionModel(TypeSelection);
    ui->tableView_2->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView_2->setSelectionMode(QAbstractItemView::SingleSelection);
    connect(TypeSelection,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
            this,SLOT(change_TypeSelection(QModelIndex,QModelIndex)));

    tabType->select();
    /*********************************************************************************/
    tabSize=new QSqlTableModel(this,db);
    tabSize->setTable("Size");
    tabSize->setSort(tabSize->fieldIndex("id"),Qt::AscendingOrder);
    tabSize->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabSize->select()))
    {
        return false;
    }

    tabSize->setHeaderData(tabSize->fieldIndex("size_id"),Qt::Horizontal,"编号(size_id)");
    tabSize->setHeaderData(tabSize->fieldIndex("size_name"),Qt::Horizontal,"规格(size_name)");

    ui->tableView_3->setModel(tabSize);
    for(int i=0;i<tabSize->columnCount();i++)
        ui->tableView_3->setColumnWidth(i,300);

    SizeSelection=new QItemSelectionModel(tabSize);
    ui->tableView_3->setSelectionModel(SizeSelection);
    ui->tableView_3->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView_3->setSelectionMode(QAbstractItemView::SingleSelection);
    connect(SizeSelection,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
            this,SLOT(change_SizeSelection(QModelIndex,QModelIndex)));

    tabSize->select();
    /*********************************************************************************/
    return true;
}

/*新增一个数据记录*/
void MainWindow::on_pushButton_2_clicked()
{
    /*数据库未打开时退出*/
    if(!db.isOpen())  return;

    auto index=ui->tabWidget->currentIndex();
    QString table_name;
    QString id_str;
    switch(index){
    case 0:
    {
        table_name="Materials";
        id_str="id";
        break;
    }
    case 1:
    {
        table_name="Type";
        id_str="type_id";
        break;
    }
    case 2:
    {
        table_name="Size";
        id_str="size_id";
        break;
    }
    }
    int id;
    query.exec("select * from "+table_name+" where "+id_str+"=1");
    if(query.next())
    {
        query.exec("select min("+id_str+"+1) from "+table_name+" t1 where not exists (select * from "+table_name+" t2 where t2."+id_str+"=t1."+id_str+"+1)");
        query.next();
        id=query.value(0).toInt();
    }
    else
    {
        id=1;
    }

    int row=0;

    /*插入新数据*/
    switch(index){
    case 0:
    {
        tabTable->insertRow(row);
        tabTable->setData(tabTable->index(row,tabTable->fieldIndex("id")),id);
        MatSelection->setCurrentIndex(tabTable->index(row,tabTable->fieldIndex("name")),my_selection_mode);
        break;
    }
    case 1:
    {
        tabType->insertRow(row);
        tabType->setData(tabType->index(row,tabType->fieldIndex("type_id")),id);
        TypeSelection->setCurrentIndex(tabType->index(row,tabType->fieldIndex("type_name")),my_selection_mode);
        break;
    }
    case 2:
    {
        tabSize->insertRow(row);
        tabSize->setData(tabSize->index(row,tabSize->fieldIndex("size_id")),id);
        SizeSelection->setCurrentIndex(tabSize->index(row,tabSize->fieldIndex("size_name")),my_selection_mode);
        break;
    }
    }
}

/*刷新*/
void MainWindow::on_actionF5_triggered()
{
    if(!db.isOpen())
    {
        return;
    }
    tabTable->select();
    tabType->select();
    tabSize->select();
}

/*删除当前记录*/
void MainWindow::on_pushButton_3_clicked()
{
    if(!db.isOpen())  return;

    QString str=ui->lineEdit->text();
    int row=ui->spinboxRow->value()-1;
    if(str=="Materials")
    {
        tabTable->removeRow(row);
    }
    if(str=="Type")
    {
        tabType->removeRow(row);
    }
    if(str=="Size")
    {
        tabSize->removeRow(row);
    }
}

/*SQL执行器*/
void MainWindow::on_pushButton_clicked()
{
    if(!db.isOpen())  return;

    if(query.exec(ui->plainTextEdit->toPlainText()))
    {
        ui->plainTextEdit_2->setPlainText("SQL语句执行成功，执行语句无输出");
        auto txt=ui->plainTextEdit_2->toPlainText();
        bool flag=true;
        while(query.next())
        {
            if(flag)
            {
                flag=false;
                txt.clear();
            }
            else
            {
                txt.append("\n");
            }
            int count=0;
            while(query.value(count).isValid())
            {
                txt.append(query.value(count).toString()+"\t");
                count++;
            }
        }
        ui->plainTextEdit_2->setPlainText(txt);
    }
    else
    {
        ui->plainTextEdit_2->setPlainText("SQL语句执行失败，请查询语句是否正确");
    }

    tabTable->select();
    tabType->select();
    tabSize->select();
}

/*搜索框*/
void MainWindow::on_lineEdit_2_textChanged(const QString &arg1)
{
    if(!db.isOpen())  return;
    if(arg1.isEmpty())
    {
        tabTable->setFilter("");
    }
    else
    {
        tabTable->setFilter(QString("\
Materials.id like '%%1%' or \
Materials.name like '%%1%' or \
Materials.mbox like '%%1%' or \
Materials.mrow like '%%1%' or \
Materials.mcol like '%%1%' or \
Materials.type_id in (select Type.type_id from Type where Type.type_name like '%%1%') or \
Materials.size_id in (select Size.size_id from Size where Size.size_name like '%%1%')").arg(arg1));
        //这里加上嵌套搜索算法
    }
    tabTable->select();
}
void MainWindow::on_lineEdit_3_textChanged(const QString &arg1)
{
    if(!db.isOpen())  return;
    if(arg1.isEmpty())
    {
        tabType->setFilter("");
    }
    else
    {
        tabType->setFilter(QString("type_id like '%%1%' or type_name like '%%1%'").arg(arg1));
    }
    tabType->select();
}
void MainWindow::on_lineEdit_4_textChanged(const QString &arg1)
{
    if(!db.isOpen())  return;
    if(arg1.isEmpty())
    {
        tabSize->setFilter("");
    }
    else
    {
        tabSize->setFilter(QString("size_id like '%%1%' or size_name like '%%1%'").arg(arg1));
    }
    tabSize->select();
}

/*一个神奇的刷新机制*/
void MainWindow::on_tabWidget_currentChanged(int index)
{
    if(!db.isOpen())  return;
    if(index==0)
    {
        tabTable=new QSqlRelationalTableModel(this,db);
        tabTable->setTable("Materials");
        tabTable->setSort(tabTable->fieldIndex("id"),Qt::AscendingOrder);
        tabTable->setEditStrategy(QSqlTableModel::OnFieldChange);
        if(!(tabTable->select()))
        {
            return;
        }

        tabTable->setHeaderData(tabTable->fieldIndex("id"),Qt::Horizontal,"编号(id)");
        tabTable->setHeaderData(tabTable->fieldIndex("name"),Qt::Horizontal,"名称(name)");
        tabTable->setHeaderData(tabTable->fieldIndex("mbox"),Qt::Horizontal,"盒子(mbox)");
        tabTable->setHeaderData(tabTable->fieldIndex("mrow"),Qt::Horizontal,"行(mrow)");
        tabTable->setHeaderData(tabTable->fieldIndex("mcol"),Qt::Horizontal,"列(mcol)");
        tabTable->setHeaderData(tabTable->fieldIndex("type_id"),Qt::Horizontal,"类型");
        tabTable->setHeaderData(tabTable->fieldIndex("size_id"),Qt::Horizontal,"规格");

        tabTable->setRelation(tabTable->fieldIndex("type_id"),QSqlRelation("Type","type_id","type_name"));
        tabTable->setRelation(tabTable->fieldIndex("size_id"),QSqlRelation("Size","size_id","size_name"));

        ui->tableView->setModel(tabTable);
        for(int i=0;i<tabTable->columnCount();i++)
            ui->tableView->setColumnWidth(i,150);

        MatSelection=new QItemSelectionModel(tabTable);
        ui->tableView->setSelectionModel(MatSelection);
        ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
        ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
        connect(MatSelection,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
                this,SLOT(change_MatSelection(QModelIndex,QModelIndex)));

        ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));

        tabTable->select();
    }
    else
    {
        tabType->select();
        tabSize->select();
    }
}

/*另存为*/
void MainWindow::on_actionSave_triggered()
{
    if(!db.isOpen())  return;
    QString time=QDateTime::currentDateTime().toString("yyyy-MM-dd hh-mm-ss");
    QString temp=QFileDialog::getSaveFileName(this, \
        QStringLiteral("选择保存位置"),\
        QCoreApplication::applicationDirPath()+"/"+time,"数据库(*.db)");
    QFile::copy(DB_path,temp);
}

